package com.tim.dao.Impl;

import com.tim.dao.PersonDao;
import com.tim.pojo.Person;
import com.tim.util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * 作者：Tim
 * 时间：2021/4/28 14:45
 */
public class PersonDaoImpl implements PersonDao {
    @Override
    public int deletePerson(int personID) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "delete from person where personID = ?";
        Object[] args = {personID};
        return qr.update(sql,args);
    }

    @Override
    public int addPerson(Person person) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql ="insert into person (personID,plotName,houseName,personName,image,idNum,tel,job,bornDate,sex,type,detail,creatorTime) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
        Object[] args={person.getPersonID(),person.getPlotName(),person.getHouseName(),person.getPersonName(),person.getImage(),person.getIdNum(),person.getTel(),person.getJob(),person.getBornDate(),person.getSex(),person.getType(),person.getDetail(),person.getCreatorTime()};
        return qr.update(sql,args);
    }

    @Override
    public Person selectOne(int personID) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from person where personID = ?";
        Object[] args = {personID};
        return qr.query(sql,new BeanHandler<Person>(Person.class),args);
    }

    @Override
    public int updatePerson(Person person) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql ="update person set plotName=?,houseName=?,personName=?,image=?,idNum=?,tel=?,job=?,bornDate=?,sex=?,type=?,detail=?,creatorTime=? where personID = ?";
        Object[] args = {person.getPlotName(),person.getHouseName(),person.getPersonName(),person.getImage(),person.getIdNum(),person.getTel(),person.getJob(),person.getBornDate(),person.getSex(),person.getType(),person.getDetail(),person.getCreatorTime(),person.getPersonID()};
        return qr.update(sql,args);
    }

    @Override
    public List<Person> searchPerson(String personName) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from person where personName like ?";
        Object[] args = {"%"+personName+"%"};
        return qr.query(sql,new BeanListHandler<Person>(Person.class),args);
    }

    @Override
    public List<Person> limit(Integer currentPage, Integer pageSize, String personName) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        if(personName == null){
            String sql = "select * from person limit ?,?";
            Object[] args={(currentPage-1)*pageSize,pageSize};
            return qr.query(sql,new BeanListHandler<Person>(Person.class),args);
        }else{
            String sql = "select * from person where personName like ? limit ?,?";
            Object[] args={"%"+personName+"%",(currentPage-1)*pageSize,pageSize};
            return qr.query(sql,new BeanListHandler<Person>(Person.class),args);
        }
    }

    @Override
    public int count(String personName) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        if(personName == null){
            String sql = "select count(*) from person ";
            //new ScalarHandler<泛型类型>()   单行单列用这个  .intValue()转换为int类型
            return qr.query(sql,new ScalarHandler<Number>()).intValue();
        }else{
            String sql = "select count(*) from person where personName like ?";
            //new ScalarHandler<泛型类型>()   单行单列用这个  .intValue()转换为int类型
            return qr.query(sql,new ScalarHandler<Number>(),"%"+personName+"%").intValue();
        }
    }
}
